﻿#pragma once
#include "stdafx.h"
#include "MainFrameDlg.h"
#include "DbManage.h"
#include "Poco/Data/SQLite/Utility.h"
#include "Poco/Data/SQLite/Notifier.h"
#include "Poco/UUIDGenerator.h"
#include "Poco/DateTimeFormatter.h"
#include "Poco/UnicodeConverter.h"
#include "Poco/Data/Transaction.h"
#include "Poco/Nullable.h"
#include <fstream>


using Poco::UnicodeConverter;
using Poco::Data::Transaction;
using Poco::Nullable;

std::shared_ptr<DbManage>	DbManage::_sp_dbmanage;
Poco::Mutex	 DbManage::_mutex;		//

static const string s_sys_password = "system_zxcvbn";
static const string s_his_password = "history_zxcvbn";
static const string s_sysdb = "wcs.db";
static const string s_historydb = "history.db";

//数据库初始化
void DbManage::DB_Init(void)
{
	Poco::Mutex::ScopedLock lk(_mutex);
	if (!_sp_dbmanage)
	{
		Poco::Data::SQLite::Connector::registerConnector();
		//开机启动时当前目录是system
		TCHAR* buff = nullptr;
		_get_tpgmptr(&buff);
		TCHAR drive[_MAX_DRIVE];
		TCHAR path[MAX_PATH];
		_tsplitpath_s(buff, drive, _MAX_DRIVE, path, MAX_PATH, nullptr, 0, nullptr, 0);
		wstring wfile = drive;
		wfile += path;	
		string sfile;
		UnicodeConverter::toUTF8(wfile, sfile);
		_sp_dbmanage = std::make_shared<DbManage>(sfile);
	}		
}


DbManage::DbManage(string dirpath)
	: _dirpath(dirpath)
	, _b_flush_to_disk(false)
	, _session_system(Poco::Data::SQLite::Connector::KEY, ":memory:")
	, _session_history(Poco::Data::SQLite::Connector::KEY, dirpath + s_historydb)
{
	//设置历史数据库的加密秘钥
	if (!Poco::Data::SQLite::Utility::fileToMemory(_session_system, dirpath + s_sysdb, s_sys_password)){
		ASSERT(false);
		rename((dirpath + s_sysdb).c_str(), (dirpath + "bad_" + s_sysdb).c_str());
	}		
	try{
		createSysTable();
	}
	catch (Poco::Data::SQLite::CorruptImageException&){
		ASSERT(false);
		rename((dirpath + s_sysdb).c_str(), (dirpath + "bad_" + s_sysdb).c_str());
	}
	//设置历史数据库的加密秘钥
	Poco::Data::SQLite::Utility::setPassword(_session_history, s_his_password);
	try{
		createHistoryTable();
	}
	catch (Poco::Data::SQLite::CorruptImageException&){
		_session_history.close();
		ASSERT(false);
		rename((dirpath + s_historydb).c_str(), (dirpath + "bad_" + s_historydb).c_str());
		_session_history.swap(Session(Poco::Data::SQLite::Connector::KEY, dirpath + s_historydb));
		Poco::Data::SQLite::Utility::setPassword(_session_history, s_sys_password);
		createHistoryTable();
	}	
}


DbManage::~DbManage()
{
	memToFile();
}

bool DbManage::memToFile()
{
	if (_b_flush_to_disk && Poco::Data::SQLite::Utility::memoryToFile(_dirpath + s_sysdb, s_sys_password, _session_system)){
		_b_flush_to_disk = false;
		return true;
	}
	return false;
}

void DbManage::createSysTable()
{
	try
	{
		//创建系统设置表
		_session_system << "CREATE TABLE IF NOT EXISTS TBSetup(ID INTEGER PRIMARY KEY,"
			"Title TEXT,LeastRecords INTEGER,SoundPath1 TEXT,SoundPath2 TEXT,SoundSel1 INTEGER,SoundSel2 INTEGER,"
			"SerialPort TEXT,Option SMALLINT,Warn1Bkcolor INTEGER,Warn1txtcolor INTEGER,"
			"Warn2Bkcolor INTEGER,Warn2txtcolor INTEGER,TimeoutBkcolor INTEGER,Timeouttxtcolor INTEGER,"
			"NoReplyBkcolor INTEGER,NoReplytxtcolor INTEGER,HasReplyBkcolor INTEGER,HasReplytxtcolor INTEGER,"
			"Warn1Time INTEGER,Warn2Time INTEGER,SevTimeout INTEGER,LCID INTEGER);"
			//创建服务类型表
			"CREATE TABLE IF NOT EXISTS TBServices(ID INTEGER PRIMARY KEY,ServiceType TEXT);"
			//创建服务区域表
			"CREATE TABLE IF NOT EXISTS TBZone(ID INTEGER PRIMARY KEY,Zone TEXT UNIQUE);"
			//创建员工表
			"CREATE TABLE IF NOT EXISTS TBEmployee(ID INTEGER PRIMARY KEY,EmpID TEXT,"
			"EmpName TEXT,Gender INTEGER,Telephone TEXT,Remark TEXT);"
			//创建值班表
			"CREATE TABLE IF NOT EXISTS TBDuty(ID INTEGER PRIMARY KEY,EmpID INTEGER,"
			"BeginTime INTEGER,EndTime INTEGER,ZoneId INTEGER);"
			//创建系统用户表
			"CREATE TABLE IF NOT EXISTS TBOperator(ID INTEGER PRIMARY KEY,Operator TEXT,Password TEXT,"
			"Post TEXT,Gender INTEGER,Authority INTEGER);"
			//创建呼叫器表
			"CREATE TABLE IF NOT EXISTS TBPager(ID INTEGER PRIMARY KEY,PagerID INTEGER,Name TEXT,ZoneId INTEGER);"
			, now;

		_session_system << "CREATE VIEW IF NOT EXISTS VWDuty AS SELECT TBDuty.*,TBZone.Zone FROM "
			"TBDuty LEFT JOIN TBZone ON TBZone.ID=TBDuty.ZoneId", now;

		_session_system << "CREATE VIEW IF NOT EXISTS VWPager AS SELECT TBPager.*,TBZone.Zone FROM "
			"TBPager LEFT JOIN TBZone ON TBZone.ID=TBPager.ZoneId", now;

		_session_system << "CREATE TRIGGER IF NOT EXISTS Tri_del_tbzone AFTER DELETE ON TBZone"
			" BEGIN "
			"DELETE FROM TBDuty where ZoneId=OLD.ID;"
			"DELETE FROM TBPager where ZoneId=OLD.ID;"
			" END;", now;

		_session_system << "CREATE TRIGGER IF NOT EXISTS Tri_del_tbemp AFTER DELETE ON TBEmployee"
			" BEGIN "
			"DELETE FROM TBDuty where EmpID=OLD.ID;"
			" END;", now;	
	}
	catch (Poco::Data::SQLite::CorruptImageException&){
		throw;
	}
	catch (...)
	{
		assert(false);
	}
}

void DbManage::createHistoryTable()
{
	try{
		_session_history << "CREATE TABLE IF NOT EXISTS TBHistory("
			"ID INTEGER PRIMARY KEY,CallTime TIMESTAMP,ProcessTime TIMESTAMP,TimeOut BOOLEAN,EmpID INTEGER,"
			"EmpName TEXT,SevStatus INTEGER,"		//服务状态
			"SevTotalSeconds INTEGER,SevType TEXT,"	//服务类型
			"PagerCode INTEGER,PagerName TEXT,Zone TEXT,Remark TEXT);"
			, now;

		//以呼叫时间字段创建索引
		_session_history << "CREATE INDEX IF NOT EXISTS idxHist ON TBHistory(ID,CallTime);"
			, now;
	}
	catch (Poco::Data::SQLite::CorruptImageException&){
		throw;
	}
	catch (...)
	{
		assert(false);
	}
}

Session& DbManage::getHistorySession()
{
	return _session_history;
}

//根据呼叫时间，服务类别，和呼叫器id得出整个呼叫对象，(服务员)
bool DbManage::getNewCall(shared_ptr<CCall>& call, Timespan calltm, int serverType, int pagerId)
{
	call->wPagerID = pagerId;
	//TBPager(ID INTEGER PRIMARY KEY,PagerId INTEGER,Name TEXT,ZoneId INTEGER)
	//TBServices(ID INTEGER PRIMARY KEY,ServiceType TEXT)
	//TBDuty(ID INTEGER PRIMARY KEY, EmpID INTEGER, BeginTime INTEGER,EndTime INTEGER,ZoneId INTEGER)
	//TBEmployee(ID INTEGER PRIMARY KEY,EmpID TEXT,EmpName TEXT,Gender INTEGER,Telephone TEXT,Remark TEXT)
	//TBZone(ID INTEGER PRIMARY KEY,Zone TEXT UNIQUE)
	try{
		_session_system << "select ServiceType, TBPager.Name, TBZone.Zone, TBEmployee.EmpID, EmpName from TBPager left join "
			"(select :pid as PID,ServiceType from TBServices where ID=:st) on PID=PagerId "
			"left join (select EmpID,ZoneId from TBDuty where :tm between BeginTime and EndTime) as duty on duty.ZoneId=TBPager.ZoneId "
			"left join TBEmployee on TBEmployee.ID=duty.EmpID "
			"left join TBZone on TBZone.ID=TBPager.ZoneId "
			"where PagerId=:pid;"
			, use(pagerId), use(serverType), bind(calltm.totalSeconds())
			, into(call->strService), into(call->strPagerName), into(call->strZone), into(call->strEmpID), into(call->strEmpName)
			, lowerLimit(1), now;	
		return true;
	}
	catch (Poco::Data::LimitException&){
		_session_system << "select ServiceType from TBServices where ID=:st"
			, use(serverType)
			, into(call->strService), limit(1)
			, now;
		std_wstring_format(call->strPagerName, _T("%d"), pagerId);
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}


void DbManage::enumServer(std::vector<std::pair<UInt64, wstring> >& servpairvec)
{
	try
	{
		_session_system << "select * from TBServices"
			, into(servpairvec), now;
	}
	catch (...)
	{
		assert(false);
	}
}

void DbManage::enumZone(std::vector<wstring>& zonevec)
{
	try
	{
		_session_system << "select Zone from TBZone", into(zonevec), now;
	}
	catch (...)
	{
		assert(false);
	}
}

void DbManage::enumZone(std::vector<std::pair<UInt64, wstring> >& zonepairvec)
{
	try
	{
		_session_system << "select * from TBZone", into(zonepairvec), now;
	}
	catch (...)
	{
		assert(false);
	}
}

bool DbManage::removeZone(wstring& zone)
{
	try{
		_session_system << "DELETE FROM TBZone WHERE Zone=:zn;", use(zone), now;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

//zone是否与呼叫器绑定,返回关联的呼叫器id
wstring DbManage::zoneBeConstranedPager(wstring& zone)
{
	wstring pagerid, name;
	try{//TBPager(ID INTEGER PRIMARY KEY,PagerId INTEGER,Name TEXT,ZoneId INTEGER)
		_session_system << "SELECT PagerId,Name from TBPager JOIN (select ID AS znid FROM TBZone WHERE Zone=:zn) ON TBPager.ZoneId=znid;"
			, use(zone), into(pagerid), into(name), lowerLimit(1), now;
		if (!name.empty()){
			pagerid.swap(name);
		}
		return std::move(name);
	}
	catch (Poco::Data::LimitException&){
		return wstring();
	}
	catch (...)
	{
		assert(false);
	}
	return wstring();
}

//zone是否正被值班使用中
wstring DbManage::zoneBeConstranedDuty(wstring& zone)
{
	wstring emp,id;
	try{
		//TBPager(ID INTEGER PRIMARY KEY,PagerId INTEGER,Name TEXT,ZoneId INTEGER)
		//TBServices(ID INTEGER PRIMARY KEY,ServiceType TEXT)
		//TBDuty(ID INTEGER PRIMARY KEY, EmpID INTEGER, BeginTime INTEGER,EndTime INTEGER,ZoneId INTEGER)
		//TBEmployee(ID INTEGER PRIMARY KEY,EmpID TEXT,EmpName TEXT,Gender INTEGER,Telephone TEXT,Remark TEXT)
		//TBZone(ID INTEGER PRIMARY KEY,Zone TEXT UNIQUE)
		_session_system << "SELECT TBEmployee.EmpID,EmpName from (SELECT ID AS tbzid FROM TBZone WHERE Zone=:zn) JOIN "
			"TBDuty ON TBDuty.ZoneId=tbzid LEFT JOIN TBEmployee ON TBDuty.EmpId=TBEmployee.ID"
			, use(zone), into(id), into(emp), lowerLimit(1), now;
		if (!emp.empty())
			return std::move(emp);
		else
			return std::move(id);
	}
	catch (Poco::Data::LimitException&){
		return wstring();
	}
	catch (...)
	{
		assert(false);
	}
	return wstring();
}

bool DbManage::zoneExists(wstring& zone)
{
	if (zone.empty()) return false;
	try
	{
		int count=0;
		_session_system << "select count(*) from TBZone where Zone = ?", into(count), use(zone), now;
		return count != 0;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

void DbManage::enumPager(std::vector<Pager_PTR>& pagervec)
{
	// VWPager(ID INTEGER PRIMARY KEY,PagerId INTEGER,Name TEXT,ZoneId INTEGER,Zone TEXT)
	try
	{
		_session_system << "select * from VWPager", into(pagervec), now;
	}
	catch (...)
	{
		assert(false);
	}
}

bool DbManage::addPager(Pager_PTR ptrPager)
{
	try
	{//TBPager(ID INTEGER PRIMARY KEY,PagerId INTEGER,Name TEXT,ZoneId INTEGER)
		ASSERT(ptrPager->nID == 0);
		ASSERT(ptrPager->wPagerID);
		Nullable<UInt64> zId,pagerid;
		Nullable<wstring> name;
		if (!ptrPager->strPager.empty())
			name = ptrPager->strPager;
		if (ptrPager->wPagerID)
			pagerid = ptrPager->wPagerID;
		if (ptrPager->nZoneId)
			zId = ptrPager->nZoneId;
		Poco::Data::Transaction transation(_session_system, true);
		Poco::Data::SQLite::Notifier notifier(_session_system);
		_session_system << "INSERT INTO TBPager (PagerId,Name,ZoneId)values(?,?,?)"
			, use(pagerid), use(name), bind(zId)
			, now;
		ptrPager->nID = notifier.getRow();
		transation.commit();
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::updatePager(Pager_PTR ptrPager)
{
	try
	{//TBPager(ID INTEGER PRIMARY KEY,PagerId INTEGER,Name TEXT,ZoneId INTEGER)
		ASSERT(ptrPager->nID);
		ASSERT(ptrPager->wPagerID);
		Nullable<UInt64> pgId, zId;
		Nullable<wstring> name;
		if (!ptrPager->strPager.empty())
			name = ptrPager->strPager;	
		if (ptrPager->wPagerID)
			pgId = ptrPager->wPagerID;
		if (ptrPager->nZoneId)
			zId = ptrPager->nZoneId;
		_session_system << "UPDATE TBPager SET PagerId=?,Name=?,ZoneId=? WHERE ID=?"
			, use(pgId), use(name), bind(zId), bind(ptrPager->nID)
			, now;
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::removePager(int pagerid)
{
	try
	{//TBPager(ID INTEGER PRIMARY KEY,PagerId INTEGER,Name TEXT,ZoneId INTEGER)
		ASSERT(pagerid);
		_session_system << "DELETE FROM TBPager WHERE PagerId=?", use(pagerid), now;
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

void DbManage::selectPagerInZoon(UInt64 zoneid, std::vector<wstring>& pagernamevec)
{
	try
	{//TBPager(ID INTEGER PRIMARY KEY,PagerId INTEGER,Name TEXT,ZoneId INTEGER)
		_session_system << "select case when Name is null then PagerId else Name end from TBPager where ZoneId = ?"
			, into(pagernamevec), use(zoneid) ,now;		
	}
	catch (...)
	{
		assert(false);
	}
}

bool DbManage::pagerExists(int pagerId)
{
	try
	{
		int count=0;
		_session_system << "select count(*) from TBPager where PagerId = ?", into(count), use(pagerId), now;
		return count != 0;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::enumEmployee(std::vector<Emp_PTR>& empvec)
{
	//ID INTEGER PRIMARY KEY,EmpID TEXT,EmpName TEXT,Gender INTEGER,Telephone TEXT,Remark TEXT)
	try{
		_session_system << "select * from TBEmployee;", into(empvec), now;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::addEmployee(Emp_PTR ptrEmp)
{
	try
	{//TBEmployee: ID INTEGER PRIMARY KEY,EmpID TEXT,EmpName TEXT,Gender INTEGER,Telephone TEXT,Remark TEXT)
		Poco::Data::Transaction transation(_session_system, true);
		Poco::Data::SQLite::Notifier notifier(_session_system);
		_session_system << "INSERT INTO TBEmployee (EmpID,EmpName,Gender,Telephone,Remark,ID)values(?,?,?,?,?,?);"
			, use(ptrEmp), now;
		ptrEmp->nID = notifier.getRow();
		transation.commit();
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::updateEmployee(Emp_PTR ptrEmp)
{
	try
	{//TBEmployee: ID INTEGER PRIMARY KEY,EmpID TEXT,EmpName TEXT,Gender INTEGER,Telephone TEXT,Remark TEXT)
		_session_system << "UPDATE TBEmployee SET EmpID=?,EmpName=?,Gender=?,Telephone=?,Remark=? WHERE ID=?;"
			, use(ptrEmp), now;
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::findEmployee(wstring& empID)
{
	try
	{//TBEmployee: ID INTEGER PRIMARY KEY,EmpID TEXT,EmpName TEXT,Gender INTEGER,Telephone TEXT,Remark TEXT)
		int count = 0;
		_session_system << "SELECT COUNT(*) FROM TBEmployee WHERE EmpID=?;"
			, use(empID), into(count), now;
		return count!=0;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

//nid为行号
bool DbManage::deleteEmployee(UInt64 nid)
{
	try
	{//TBEmployee: ID INTEGER PRIMARY KEY,EmpID TEXT,EmpName TEXT,Gender INTEGER,Telephone TEXT,Remark TEXT)
		_session_system << "DELETE FROM TBEmployee WHERE ID=?;"
			, use(nid), now;
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

void DbManage::enumDuty(std::vector<Duty_PTR>& dutyvec)
{
	//VWDuty: ID INTEGER PRIMARY KEY,EmpID INTEGER,BeginTime INTEGER,EndTime INTEGER,ZoneId INTEGER,Zone TEXT
	try
	{
		_session_system << "select * from VWDuty", into(dutyvec), now;
	}
	catch (...)
	{
		assert(false);
	}
}

bool DbManage::addDuty(Duty_PTR ptrDuty)
{
	try
	{//TBDuty: ID INTEGER PRIMARY KEY,EmpID INTEGER,BeginTime INTEGER,EndTime INTEGER,ZoneId INTEGER
		Poco::Data::Transaction transation(_session_system, true);
		Poco::Data::SQLite::Notifier notifier(_session_system);
		_session_system << "INSERT INTO TBDuty (EmpID,BeginTime,EndTime,ZoneId)values(?,?,?,?);"
			, bind(ptrDuty->nEmptabId), bind(ptrDuty->clkBeginTime.totalSeconds())
			, bind(ptrDuty->clkEndTime.totalSeconds())
			, bind(ptrDuty->nZoneId), now;
		ptrDuty->nID = notifier.getRow();
		transation.commit();
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

//nid为行号
bool DbManage::deleteDuty(UInt64 nid)
{
	try
	{
		_session_system << "DELETE FROM TBDuty WHERE ID=?;"
			, use(nid), now;
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::updateDuty(Duty_PTR ptrDuty)
{
	try
	{//TBDuty: ID INTEGER PRIMARY KEY,EmpID INTEGER,BeginTime INTEGER,EndTime INTEGER,ZoneId INTEGER
		_session_system << "UPDATE TBDuty SET EmpID=?,BeginTime=?,EndTime=?,ZoneId=? WHERE ID=?;"
			, bind(ptrDuty->nEmptabId), bind(ptrDuty->clkBeginTime.totalSeconds())
			, bind(ptrDuty->clkEndTime.totalSeconds())
			, bind(ptrDuty->nZoneId), bind(ptrDuty->nID)
			, now;
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::enumUntreatedCall(std::vector<Call_PTR>& callvec)
{
	//"ID INTEGER PRIMARY KEY,CallTime TIMESTAMP,ProcessTime TIMESTAMP,TimeOut BOOLEAN,EmpID INTEGER,"\
	//			"EmpName TEXT,SevStatus INTEGER,SevTotalSeconds INTEGER,SevType INTEGER,PagerName TEXT,"\
	//			"PagerCode INTEGER,Zone TEXT,Remark TEXT);"
	int sevstatus = Untreated;
	try
	{
		_session_history << "SELECT ID,CallTime,ProcessTime,TimeOut,EmpID,EmpName,SevStatus,SevTotalSeconds,SevType,"
			"PagerCode,PagerName,Zone,Remark from TBHistory where SevStatus=? and datetime(CallTime,'localtime') > datetime('now','localtime','start of day');"
			, use(sevstatus), into(callvec), now;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}


void DbManage::getSetup()
{
	/*"Title TEXT, INTEGER, TEXT, TEXT, INTEGER, INTEGER,"\
		" TEXT,Option SMALLINT,Warn1Bkcolor INTEGER,Warn1txtcolor INTEGER,"\
		"Warn2Bkcolor INTEGER,Warn2txtcolor INTEGER,TimeoutBkcolor INTEGER,Timeouttxtcolor INTEGER,"\
		"NoReplyBkcolor INTEGER,NoReplytxtcolor INTEGER,HasReplyBkcolor INTEGER,HasReplytxtcolor INTEGER,"\
		"Warn1Time INTEGER,Warn2Time INTEGER,SevTimeout INTEGER,LCID INTEGER);"\*/
	CMainFrameDlg* maindlg = g_pMainDlg;
	try{
		Poco::UInt64 Warn1Time, Warn2Time, SevTimeout;
		int lcid = 0;
		_session_system << "select Title,LeastRecords,SoundPath1 ,SoundPath2 ,SoundSel1 ,SoundSel2,"
			"SerialPort ,Option ,Warn1Bkcolor ,Warn1txtcolor ,Warn2Bkcolor ,Warn2txtcolor ,TimeoutBkcolor ,Timeouttxtcolor ,"
			"NoReplyBkcolor ,NoReplytxtcolor ,HasReplyBkcolor ,HasReplytxtcolor ,Warn1Time ,Warn2Time ,SevTimeout ,LCID "
			" from TBSetup", into(maindlg->m_strTitle), into(maindlg->m_nLatestRecords), into(maindlg->m_strWarnSound1)
			, into(maindlg->m_strWarnSound2), into(maindlg->m_nSoundState1), into(maindlg->m_nSoundState2)
			, into(maindlg->m_strSerialPort), into(maindlg->m_Option.bt), into(maindlg->m_dwSevWarn1Bkcolor)
			, into(maindlg->m_dwSevWarn1Txtcolor), into(maindlg->m_dwSevWarn2Bkcolor), into(maindlg->m_dwSevWarn2Txtcolor)
			, into(maindlg->m_dwSevTimeoutBkcolor), into(maindlg->m_dwSevTimeoutTxtcolor)
			, into(maindlg->m_dwNoReplyBkcolor), into(maindlg->m_dwNoReplyTxtcolor)
			, into(maindlg->m_dwHasReplyBkcolor), into(maindlg->m_dwHasReplyTxtcolor)
			, into(Warn1Time), into(Warn2Time), into(SevTimeout), into(lcid)
			, lowerLimit(1), now;
		if (lcid)
			maindlg->m_lcid = lcid;
		maindlg->m_clkWarn1.assign(Warn1Time, 0);
		maindlg->m_clkWarn2.assign(Warn2Time, 0);
		maindlg->m_clkSevTimeout.assign(SevTimeout, 0);
	}
	catch (Poco::Data::LimitException&)
	{
		_session_system << "Insert into TBSetup (Title,LeastRecords,SoundPath1,SoundPath2,SoundSel1,SoundSel2,"
			"Option,Warn1Bkcolor,Warn1txtcolor,Warn2Bkcolor,Warn2txtcolor,TimeoutBkcolor,Timeouttxtcolor,"
			"NoReplyBkcolor,NoReplytxtcolor,HasReplyBkcolor,HasReplytxtcolor,Warn1Time,Warn2Time,SevTimeout,LCID)"
			"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"
			, use(maindlg->m_strTitle), use(maindlg->m_nLatestRecords), use(maindlg->m_strWarnSound1)
			, use(maindlg->m_strWarnSound2), use(maindlg->m_nSoundState1), use(maindlg->m_nSoundState2)
			, use(maindlg->m_Option.bt), use(maindlg->m_dwSevWarn1Bkcolor)
			, use(maindlg->m_dwSevWarn1Txtcolor), use(maindlg->m_dwSevWarn2Bkcolor), use(maindlg->m_dwSevWarn2Txtcolor)
			, use(maindlg->m_dwSevTimeoutBkcolor), use(maindlg->m_dwSevTimeoutTxtcolor), use(maindlg->m_dwNoReplyBkcolor)
			, use(maindlg->m_dwNoReplyTxtcolor), use(maindlg->m_dwHasReplyBkcolor), use(maindlg->m_dwHasReplyTxtcolor)
			, bind(maindlg->m_clkWarn1.totalSeconds()), bind(maindlg->m_clkWarn2.totalSeconds())
			, bind(maindlg->m_clkSevTimeout.totalSeconds()), bind((UInt32)maindlg->m_lcid)
			, now;
		_b_flush_to_disk = true;
	}
	catch (...)
	{
		assert(false);
	}
}

void DbManage::setSetup()
{
	CMainFrameDlg* maindlg = g_pMainDlg;
	try{
		Poco::Nullable<wstring>  Title, WarnSound1, WarnSound2, SerialPort;
		if (!maindlg->m_strTitle.empty()) Title = maindlg->m_strTitle;
		if (!maindlg->m_strWarnSound1.empty()) WarnSound1 = maindlg->m_strWarnSound1;
		if (!maindlg->m_strWarnSound2.empty()) WarnSound2 = maindlg->m_strWarnSound2;
		if (!maindlg->m_strSerialPort.empty()) SerialPort = maindlg->m_strSerialPort;
		Poco::Data::Transaction transation(_session_system, true);
		Poco::Data::SQLite::Notifier notifier(_session_system);
		_session_system << "UPDATE TBSetup SET Title=?,LeastRecords=?,SoundPath1=? ,SoundPath2=? ,SoundSel1=? ,SoundSel2=?,"
			"SerialPort =?,Option =?,Warn1Bkcolor =?,Warn1txtcolor =?,Warn2Bkcolor =?,Warn2txtcolor =?,TimeoutBkcolor =?,Timeouttxtcolor =?,"
			"NoReplyBkcolor =?,NoReplytxtcolor =?,HasReplyBkcolor =?,HasReplytxtcolor =?,Warn1Time =?,Warn2Time =?,SevTimeout =?,LCID=?;"
			, use(Title), use(maindlg->m_nLatestRecords), use(WarnSound1)
			, use(WarnSound2), use(maindlg->m_nSoundState1), use(maindlg->m_nSoundState2)
			, use(SerialPort), use(maindlg->m_Option.bt), use(maindlg->m_dwSevWarn1Bkcolor)
			, use(maindlg->m_dwSevWarn1Txtcolor), use(maindlg->m_dwSevWarn2Bkcolor), use(maindlg->m_dwSevWarn2Txtcolor)
			, use(maindlg->m_dwSevTimeoutBkcolor), use(maindlg->m_dwSevTimeoutTxtcolor)
			, use(maindlg->m_dwNoReplyBkcolor), use(maindlg->m_dwNoReplyTxtcolor)
			, use(maindlg->m_dwHasReplyBkcolor), use(maindlg->m_dwHasReplyTxtcolor)
			, bind(maindlg->m_clkWarn1.totalSeconds()), bind(maindlg->m_clkWarn2.totalSeconds())
			, bind(maindlg->m_clkSevTimeout.totalSeconds()), bind((UInt32)maindlg->m_lcid)
			, now;
		/*"Title TEXT, INTEGER, TEXT, TEXT, INTEGER, INTEGER,"\
		" TEXT,Option SMALLINT,Warn1Bkcolor INTEGER,Warn1txtcolor INTEGER,"\
		"Warn2Bkcolor INTEGER,Warn2txtcolor INTEGER,TimeoutBkcolor INTEGER,Timeouttxtcolor INTEGER,"\
		"NoReplyBkcolor INTEGER,NoReplytxtcolor INTEGER,HasReplyBkcolor INTEGER,HasReplytxtcolor INTEGER,"\
		"Warn1Time INTEGER,Warn2Time INTEGER,SevTimeout INTEGER,LCID INTEGER);"\*/
		if (!notifier.getRow()){
			_session_system << "Insert into TBSetup (Title,LeastRecords,SoundPath1,SoundPath2,SoundSel1,SoundSel2,"
				"SerialPort,Option,Warn1Bkcolor,Warn1txtcolor,Warn2Bkcolor,Warn2txtcolor,TimeoutBkcolor,Timeouttxtcolor,"
				"NoReplyBkcolor,NoReplytxtcolor,HasReplyBkcolor,HasReplytxtcolor,Warn1Time,Warn2Time,SevTimeout,LCID)"
				"VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?);"
				, use(Title), use(maindlg->m_nLatestRecords), use(WarnSound1)
				, use(WarnSound2), use(maindlg->m_nSoundState1), use(maindlg->m_nSoundState2)
				, use(SerialPort), use(maindlg->m_Option.bt), use(maindlg->m_dwSevWarn1Bkcolor)
				, use(maindlg->m_dwSevWarn1Txtcolor), use(maindlg->m_dwSevWarn2Bkcolor), use(maindlg->m_dwSevWarn2Txtcolor)
				, use(maindlg->m_dwSevTimeoutBkcolor), use(maindlg->m_dwSevTimeoutTxtcolor), use(maindlg->m_dwNoReplyBkcolor)
				, use(maindlg->m_dwNoReplyTxtcolor), use(maindlg->m_dwHasReplyBkcolor), use(maindlg->m_dwHasReplyTxtcolor)
				, bind(maindlg->m_clkWarn1.totalSeconds()), bind(maindlg->m_clkWarn2.totalSeconds())
				, bind(maindlg->m_clkSevTimeout.totalSeconds()), bind((UInt32)maindlg->m_lcid)
				, now;
		}
		transation.commit();
		_b_flush_to_disk = true;
	}
	catch (...)
	{
		assert(false);
	}
}


void DbManage::findOperator(wstring& userName, function<void(wstring&, wstring&, bool, UInt64)> fn)
{
	try
	{//TBOperator(ID INTEGER PRIMARY KEY,Operator TEXT,Password TEXT, Post TEXT,Gender INTEGER,Authority INTEGER
		bool gender;
		UInt64 Authority = 0;
		wstring name,password,post;
		wstring strUP = userName;
		_wcsupr_s(&(*strUP.begin()),strUP.size()+1);
		Statement stmt = (_session_system << "SELECT Password,Post,Gender,Authority FROM TBOperator where upper(Operator)=?"
			, use(strUP), into(password), into(post), into(gender), into(Authority), limit(1));
		while (stmt.execute()){
			fn(password, post, gender, Authority);
			if (stmt.done())
				break;
		}		
	}
	catch (...)
	{
		assert(false);
	}
}

void DbManage::EnumOperator(std::vector<wstring>& operatorvec)
{
	try
	{//TBOperator(ID INTEGER PRIMARY KEY,Operator TEXT,Password TEXT, Post TEXT,Gender INTEGER,Authority INTEGER
		_session_system << "SELECT Operator FROM TBOperator;", into(operatorvec), now;
	}
	catch (...)
	{
		assert(false);
	}
}

void DbManage::EnumOperatorAll(std::vector<stUser*>& puservec)
{
	try
	{//TBOperator(ID INTEGER PRIMARY KEY,Operator TEXT,Password TEXT, Post TEXT,Gender INTEGER,Authority INTEGER
		_session_system << "SELECT ID,Operator,Password,Post,Gender,Authority FROM TBOperator;", into(puservec), now;
	}
	catch (...)
	{
		assert(false);
	}
}

bool DbManage::removeOperator(UInt64 nId)
{
	try
	{//TBOperator(ID INTEGER PRIMARY KEY,Operator TEXT,Password TEXT, Post TEXT,Gender INTEGER,Authority INTEGER
		_session_system << "DELETE FROM TBOperator WHERE ID=?;", use(nId), now;
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::addOperator(stUser* pUser)
{
	try
	{
		Poco::Data::Transaction transation(_session_system, true);
		Poco::Data::SQLite::Notifier notifier(_session_system);
		_session_system << "INSERT INTO TBOperator (Operator,Password,Post,Gender,Authority,ID)values(?,?,?,?,?,?)"
			, use(pUser), now;
		pUser->nID = notifier.getRow();
		transation.commit();
		_b_flush_to_disk = true;
		assert(pUser->nID);
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::updateOperator(stUser* pUser)
{
	try
	{
		assert(pUser->nID);
		_session_system << "UPDATE TBOperator SET Operator=:na,Password=:pw,Post=:po,Gender=:gn,Authority=:au WHERE ID=:id;"
			, use(pUser), now;
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::updateOperatorAuthority(UInt64 nid, UInt64 authority)
{
	try{
		assert(nid);
		_session_system << "UPDATE TBOperator SET Authority=:au WHERE ID=:id;"
			, use(authority), use(nid), now;
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::updateOperatorPassword(UInt64 nid, wstring& pw)
{
	try
	{
		assert(nid);
		_session_system << "UPDATE TBOperator SET Password=:pw WHERE ID=:id;"
			, use(pw), use(nid), now;
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

void DbManage::initAdmin()
{
	try
	{//TBOperator(ID INTEGER PRIMARY KEY,Operator TEXT,Password TEXT, Post TEXT,Gender INTEGER,Authority INTEGER
		_session_system << "INSERT INTO TBOperator SELECT NULL,\"admin\",NULL,NULL,1,4294967295 where "
			"NOT EXISTS (SELECT * FROM TBOperator WHERE UPPER(Operator)==\"ADMIN\");"
			, now;
		_b_flush_to_disk = true;
	}
	catch (...)
	{
		assert(false);
	}
}

void DbManage::updateServiceType(int id, wstring& server)
{
	try
	{
		Poco::Data::Transaction transation(_session_system, true);
		Poco::Data::SQLite::Notifier notifier(_session_system);
		_session_system << "UPDATE TBServices SET ServiceType=? where ID=?", use(server), use(id), now;
		if (!notifier.getRow()){
			_session_system << "INSERT INTO TBServices (ID,ServiceType)VALUES(?,?)", use(id), use(server), now;
		}
		transation.commit();
		_b_flush_to_disk = true;
	}
	catch (...)
	{
		assert(false);
	}
}

DWORD DbManage::getLcid()
{
	DWORD lcid = 0;
	try{
		_session_system << "SELECT LCID FROM TBSetup;", into(lcid), limit(1), now;
		return lcid;
	}
	catch (...)
	{
		return 0;
	}
}


bool DbManage::backupSystem(string sfile)
{
	return Poco::Data::SQLite::Utility::memoryToFile(sfile, s_sys_password, _session_system);
}

bool DbManage::restoreSystem(wstring sfile)
{
	string f;
	UnicodeConverter::toUTF8(sfile, f);
	if (Poco::Data::SQLite::Utility::fileToMemory(_session_system, f, s_sys_password)){
		createSysTable();
		_b_flush_to_disk = true;
		return true;
	}
	return false;
}

bool DbManage::backupHistory(string sfile)
{
	return Poco::Data::SQLite::Utility::copyTo(sfile, _session_history, s_his_password);
}



bool DbManage::restoreHistory(wstring sfile)
{
	string sourfile;
	UnicodeConverter::toUTF8(sfile, sourfile);
	return Poco::Data::SQLite::Utility::recoveredFromFile(_session_history, sourfile, s_his_password);
}

wstring DbManage::getAdminPassword()
{
	//ID INTEGER PRIMARY KEY,Operator TEXT,Password TEXT,Post TEXT,Gender INTEGER,Authority INTEGER
	try{
		wstring password;
		_session_system << "SELECT Password from TBOperator where upper(Operator)=\"ADMIN\";"
			, into(password), limit(1), now;
		return password;
	}
	catch (...)
	{
		return wstring();
	}
}

UInt64 DbManage::addZone(wstring zone)
{
	try{
		//TBZone(ID INTEGER PRIMARY KEY,Zone TEXT)
		if (zone.empty()) return 0;
		Poco::Data::Transaction transation(_session_system, true);
		Poco::Data::SQLite::Notifier notifier(_session_system);
		_session_system << "INSERT INTO TBZone (Zone)VALUES(?);", use(zone), now;
		notifier.disableAll();
		transation.commit();
		_b_flush_to_disk = true;
		return notifier.getRow();
	}
	catch (...)
	{
		assert(false);
	}
	return 0;
}

bool DbManage::updateZone(wstring& oldZone, wstring& newZone)
{
	try{
		//TBZone(ID INTEGER PRIMARY KEY,Zone TEXT)
		_session_system << "UPDATE TBZone SET Zone=? WHERE Zone=?;", use(newZone), use(oldZone), now;
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::updateZone(UInt64 zoneId, wstring& newZone)
{
	try{
		//TBZone(ID INTEGER PRIMARY KEY,Zone TEXT)
		_session_system << "UPDATE TBZone SET Zone=? WHERE ID=?;", use(newZone), use(zoneId), now;
		_b_flush_to_disk = true;
		return true;
	}
	catch (...)
	{
		assert(false);
	}
	return false;
}

bool DbManage::clearExpiredData(SYSTEMTIME tm)
{
	try{
		LocalDateTime dt(tm.wYear, tm.wMonth, tm.wDay, tm.wHour, tm.wMinute, tm.wSecond, tm.wMilliseconds);
		_session_history << "delete from TBHistory where Calltime < ?;", bind(dt.utc()), now;
		return true;
	}
	catch (...)
	{
		return false;
	}
}

void DbManage::saveCall(shared_ptr<CCall> call)
{
	try{
		//	tmprocess = call->tmProcessed.utc();
		if (call->nID){		
			_session_history << "UPDATE TBHistory SET CallTime=?,ProcessTime=?,TimeOut=?,EmpID=?,EmpName=?,"\
				"SevStatus=?,SevTotalSeconds=?,SevType=?,PagerCode=?,PagerName=?,Zone=?,Remark=? where ID=?;"
				, use(call), now;
		}
		else{
			Poco::Data::Transaction transation(_session_history, true);
			Poco::Data::SQLite::Notifier notifier(_session_history);
			_session_history << "INSERT INTO TBHistory (CallTime,ProcessTime,TimeOut,EmpID,EmpName,"\
				"SevStatus,SevTotalSeconds,SevType,PagerCode,PagerName,Zone,Remark,ID) VALUES(?,?,?,?,?,?,?,?,?,?,?,?,?);"
				, use(call), now;
			call->nID = notifier.getRow();
			transation.commit();
		}	
	}
	catch (...)
	{
		assert(false);
	}
}

void DbManage::saveCallRemark(shared_ptr<CCall> call)
{
	try{
		Poco::Nullable<wstring>  remarks;
		if (!call->sRemarks.empty()) remarks = call->sRemarks;
		assert(call->nID);
		if (!call->sRemarks.empty() && call->nID){
			_session_history << "UPDATE TBHistory SET Remark=? WHERE ID=?", use(remarks), use(call->nID), now;
		}		
	}
	catch (...)
	{
		assert(false);
	}
}
